﻿IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'usp_UPDMS_REF_REPORT_Ref2010r_Get_Yearly_Tour')
	BEGIN
		DROP Procedure usp_UPDMS_REF_REPORT_Ref2010r_Get_Yearly_Tour
	END
GO

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
GO
/******************************************************************************
**	Name : usp_UPDMS_REF_REPORT_Ref2010r_Get_Yearly_Tour
**	Desc : 기행기 연도별조회
**	Test Exec Query : Exec usp_UPDMS_REF_REPORT_Ref2010r_Get_Yearly_Tour 'SP001,SP002', '2013-01', '', '', '', ''
**	Called by : Ref_Dac_UPDMS_REF_REPORT_Ref2010r.cs
**	Program ID : Ref2010r
**	Auth : 송시명
**	Date : 2013-11-06
*******************************************************************************
**	Change History
*******************************************************************************
**	Date:		Author:		Description:
**	--------	--------	---------------------------------------
**
*******************************************************************************/
CREATE PROC [dbo].[usp_UPDMS_REF_REPORT_Ref2010r_Get_Yearly_Tour]
@ls_shop_id nvarchar(max),
@ls_base_ym nvarchar(7),
@ls_nick_nm nvarchar(200),
@ls_homerun_yn nvarchar(5),
@ls_satisfaction nvarchar(3),
@ls_contents nvarchar(max),
@ls_keyword nvarchar(2000)
AS
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
SET NOCOUNT ON

DECLARE @shop_ids nvarchar(max)
IF @ls_shop_id = ''
BEGIN
    SET @shop_ids = '%'
END
ELSE
BEGIN
    SET @shop_ids = @ls_shop_id
END

SET @ls_base_ym = LEFT(@ls_base_ym, 4)

SELECT ISNULL([일], '') AS Sun,
       ISNULL([월], '') AS Mon,
       ISNULL([화], '') AS Tue,
       ISNULL([수], '') AS Wed,
       ISNULL([목], '') AS Thu,
       ISNULL([금], '') AS Fri,
       ISNULL([토], '') AS Sat
  FROM (
       SELECT z.Day_K_Abb_Nm,
              umd.Week_K_Nm,
              '<span' + CASE LEFT(CONVERT(VARCHAR, GETDATE(), 121), 7) WHEN LEFT(umd.Dt, 7) THEN ' id="this_month" ' ELSE '' END +
			  ' class="' + CASE umd.Holiday_Yn WHEN 1 THEN 'color_red ' ELSE '' END +
              CASE LEFT(CONVERT(VARCHAR, GETDATE(), 121), 10) WHEN umd.Dt THEN 'today ' ELSE '' END + 'dt">' + REPLACE(RIGHT(umd.Dt, 5),'-','/') + '</span><br />' + z.Contents AS Contents
         FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
         LEFT OUTER JOIN
              (
              SELECT DISTINCT Day_K_Abb_Nm,
                     Dt,
                     REPLACE(REPLACE(ISNULL(Contents,''), '&lt;', '<'),'&gt;', '>') AS Contents
                FROM (
                     SELECT umd.Day_K_Abb_Nm,
                            umd.Dt,
                            ( SELECT LTRIM(Contents) + '' AS [text()]
                                FROM (
                                     SELECT TOP 100 Tour_Dt,
                                            '<div class="hand" onclick="viewDetail(''' + CONVERT(NVARCHAR, urt.Seq) + ''')">●' + urs.Shop_Nm + '['
                                            + urt.Nick_Nm + ']'
                                            + CASE urt.HomeRun_Yn WHEN 'Y' THEN '<span class="sicon_heart_s"></span>' ELSE '' END
                                            + CASE WHEN dbo.ufn_UPDMS_Get_Exist_Attach_File_Image(urt.Seq, 'Ref1012m') > 0 THEN '<span class="sicon_fdisk_s"></span>' ELSE '' END
                                            + '</div>'
                                            AS Contents
                                       FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
                                       JOIN UPDMS_REF_SHOP AS urs WITH(NOLOCK) ON urt.Shop_Id = urs.Shop_Id
                                      WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
                                            OR urt.Shop_Id LIKE @shop_ids
                                            )
                                        AND LEFT(urt.Tour_Dt, 4) = @ls_base_ym
                                        AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
                                        AND urt.HomeRun_Yn LIKE @ls_homerun_yn + '%'
										AND urt.Satisfaction >= @ls_satisfaction
                                        AND urt.Contents LIKE '%' + @ls_contents + '%'
                                        AND urt.Keyword LIKE '%' + @ls_keyword + '%'
                                      ORDER BY urt.Tour_Dt, urt.Tour_Hour
                                     ) tmp
                               WHERE tmp.Tour_Dt = ucib.Tour_Dt FOR XML PATH('')
                             ) AS Contents
                        FROM UPDMS_MST_DATE AS umd WITH(NOLOCK)
                        LEFT OUTER JOIN
                             (
                             SELECT Tour_Dt
                               FROM UPDMS_REF_TOUR AS urt WITH(NOLOCK)
                              WHERE ( urt.Shop_Id IN (SELECT Rslt FROM dbo.ufn_UPDMS_Get_Rslt_At_Comma(@shop_ids))
                                    OR urt.Shop_Id LIKE @shop_ids
                                    )
                                AND LEFT(Tour_Dt, 4) = @ls_base_ym
                                AND urt.Nick_Nm LIKE '%' + @ls_nick_nm + '%'
                                AND urt.HomeRun_Yn LIKE @ls_homerun_yn + '%'
								AND urt.Satisfaction >= @ls_satisfaction
                                AND urt.Contents LIKE '%' + @ls_contents + '%'
                                AND urt.Keyword LIKE '%' + @ls_keyword + '%'
                             ) AS ucib
                          ON umd.Dt = ucib.Tour_Dt
                       WHERE umd.Yyyy = @ls_base_ym
                   ) AS rslt
              ) AS z
           ON umd.Dt = z.Dt
        WHERE umd.Yyyy = @ls_base_ym
       ) AS pv
 PIVOT ( MAX(pv.Contents) FOR pv.Day_K_Abb_Nm IN ([일],[월],[화],[수],[목],[금],[토])
       ) AS pvt

GO